package com.api.gbhg.liuwenbo.api;

import com.alibaba.fastjson.JSON;
import com.api.gbhg.liuwenbo.dao.DataBaseUtil;
import com.api.gbhg.liuwenbo.pojo.BudgetSection;
import com.api.gbhg.liuwenbo.pojo.BudgetSectionSubtotal;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description 预算科支出项目预算导出报表
 * @Author lwb
 * @Date 2021.11.24
 */
@Path("gb/BudgetSectionExport")
public class BudgetSectionItemExportApi {

    private Map<String,Object> msgMap ;

    @POST
    @Path("/exportExcel")
    @Produces(MediaType.APPLICATION_JSON)
    public String exportExcel(@Context HttpServletResponse response,Map<String,Object> param){
        msgMap = new HashMap<>();

        if (param == null ||!param.containsKey("nf") || !param.containsKey("sbdw")){
            msgMap.put("code","500");
            msgMap.put("msg","缺少参数 请检查参数是否完整！");
            msgMap.put("data","");
            return JSON.toJSONString(msgMap);
        }

        BudgetSectionItemListApi bsi = new BudgetSectionItemListApi();
        bsi.loadWorkflowId();
        List<BudgetSectionSubtotal> data = null;
        try {
            List<BudgetSection> sectionsData = bsi.getSectionsData(param);
            data = bsi.setSortAndSubtotal(sectionsData);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String msgmap = downLoadExcel1(response, data,param);
        return msgmap;
    }

    /**
     * 浏览器导出
     * @param response
     * @param data
     */
    public String downLoadExcel1(HttpServletResponse response,List<BudgetSectionSubtotal> data,Map<String,Object> param)  {

        final Logger logger = LoggerFactory.getLogger(BudgetSectionItemExportApi.class);

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("预算科支出项目预算表（不含执行率）");


            //新增数据行，并且设置单元格数据
            HSSFRow row = sheet.createRow(0);
            //定义单元格风格
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            //设置水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置字体
            HSSFFont font = workbook.createFont();
            //设置字体名称
//            font.setFontName("华文行楷");
            //设置字号
            font.setFontHeightInPoints((short)11);
            //设置是否为斜体
            font.setItalic(false);
            //设置是否加粗
            font.setBold(true);

            //设置字体颜色
            font.setColor(IndexedColors.BLACK.index);
            //设置行的高度
            cellStyle.setFont(font);
            row.setHeightInPoints(20);
            //设置默认的列宽度
            sheet.setDefaultColumnWidth(9);
            sheet.setColumnWidth(1,900*10);
            sheet.setColumnWidth(2,900*10);
            sheet.setColumnWidth(3,600*10);
            sheet.setColumnWidth(4,1100*10);
            sheet.setColumnWidth(5,500*10);
            sheet.setColumnWidth(6,1300*10);
            sheet.setColumnWidth(7,700*10);
            sheet.setColumnWidth(12,700*10);

            //合并 四个参数（分别为起始行，结束行，起始列，结束列）
            CellRangeAddress region = new CellRangeAddress(0, 0, 1, 2);
            sheet.addMergedRegion(region);

            //添加excel表头第一行
            HSSFCell cellNf = row.createCell(1);
            cellNf.setCellStyle(cellStyle);
            String nfVle = param.get("nf").equals("")?"年份："+ LocalDate.now().getYear() :"年份："+param.get("nf");
            HSSFRichTextString nf = new HSSFRichTextString(nfVle);
            cellNf.setCellValue(nf);


            HSSFCell cellSbdw = row.createCell(4);
            cellSbdw.setCellStyle(cellStyle);
            String sbdwVle = param.get("sbdw").equals("")?"申报单位：-":"申报单位："+DataBaseUtil.getSubcompanyNameById(param.get("sbdw").toString());
            HSSFRichTextString sbdw = new HSSFRichTextString(sbdwVle);
            cellSbdw.setCellValue(sbdw);


            HSSFCell cellJedw = row.createCell(10);
            cellJedw.setCellStyle(cellStyle);
            HSSFRichTextString jedw = new HSSFRichTextString("单位:万元");
            cellJedw.setCellValue(jedw);

            //-----------添加excel表头第二行

            HSSFRow row1 = sheet.createRow(1);
            HSSFRow row2 = sheet.createRow(2);
            row1.setHeightInPoints(20);
            row2.setHeightInPoints(20);

            /*
            HSSFCell cellXh = row1.createCell(0);
            cellXh.setCellStyle(cellStyle);
            HSSFRichTextString xh = new HSSFRichTextString("序号");
            cellXh.setCellValue(xh);
            CellRangeAddress region0 = new CellRangeAddress(1, 2, 0, 0);
            sheet.addMergedRegion(region0);

            HSSFCell celldw = row1.createCell(1);
            celldw.setCellStyle(cellStyle);
            HSSFRichTextString sbdw2 = new HSSFRichTextString("申报单位");
            celldw.setCellValue(sbdw2);
            CellRangeAddress region1 = new CellRangeAddress(1, 2, 1, 1);
            sheet.addMergedRegion(region1);

            HSSFCell cellXmm = row1.createCell(2);
            cellXmm.setCellStyle(cellStyle);
            HSSFRichTextString xmm = new HSSFRichTextString("项目名");
            cellXmm.setCellValue(xmm);
            CellRangeAddress region2 = new CellRangeAddress(1, 2, 2, 2);
            sheet.addMergedRegion(region2);

            HSSFCell cellZe = row1.createCell(3);
            cellZe.setCellStyle(cellStyle);
            HSSFRichTextString ze = new HSSFRichTextString("总额");
            cellZe.setCellValue(ze);
            CellRangeAddress region3 = new CellRangeAddress(1, 2, 3, 3);
            sheet.addMergedRegion(region3);

            HSSFCell cellHpqd = row1.createCell(4);
            cellHpqd.setCellStyle(cellStyle);
            HSSFRichTextString hpqd = new HSSFRichTextString("项目核批渠道");
            cellHpqd.setCellValue(hpqd);
            CellRangeAddress region4 = new CellRangeAddress(1, 2, 4, 4);
            sheet.addMergedRegion(region4);

            HSSFCell cellHpqdje = row1.createCell(5);
            cellHpqdje.setCellStyle(cellStyle);
            HSSFRichTextString hpqdje = new HSSFRichTextString("核批渠道金额");
            cellHpqdje.setCellValue(hpqdje);
            CellRangeAddress region5 = new CellRangeAddress(1, 2, 5, 5);
            sheet.addMergedRegion(region5);

            HSSFCell cellZfcg = row1.createCell(6);
            cellZfcg.setCellStyle(cellStyle);
            HSSFRichTextString zfcg = new HSSFRichTextString("政府采购");
            cellZfcg.setCellValue(zfcg);
            CellRangeAddress region6 = new CellRangeAddress(1, 1, 6, 9);
            sheet.addMergedRegion(region6);


            HSSFCell cellHwgz = row2.createCell(6);
            cellHwgz.setCellStyle(cellStyle);
            HSSFRichTextString hwgz = new HSSFRichTextString("货物购置");
            cellHwgz.setCellValue(hwgz);

            HSSFCell cellGc = row2.createCell(7);
            cellGc.setCellStyle(cellStyle);
            HSSFRichTextString gc = new HSSFRichTextString("工程");
            cellGc.setCellValue(gc);

            HSSFCell cellFw = row2.createCell(8);
            cellFw.setCellStyle(cellStyle);
            HSSFRichTextString fw = new HSSFRichTextString("服务");
            cellFw.setCellValue(fw);

            HSSFCell cellXecg = row2.createCell(9);
            cellXecg.setCellStyle(cellStyle);
            HSSFRichTextString xecg = new HSSFRichTextString("小额采购");
            cellXecg.setCellValue(xecg);

            HSSFCell cellGmfw = row1.createCell(10);
            cellGmfw.setCellStyle(cellStyle);
            HSSFRichTextString gmfw = new HSSFRichTextString("政府购买服务");
            cellGmfw.setCellValue(gmfw);
            CellRangeAddress region7 = new CellRangeAddress(1, 2, 10, 10);
            sheet.addMergedRegion(region7);
             */
            HSSFCell cellXh = row1.createCell(0);
            cellXh.setCellStyle(cellStyle);
            HSSFRichTextString xh = new HSSFRichTextString("序号");
            cellXh.setCellValue(xh);
            CellRangeAddress region0 = new CellRangeAddress(1, 2, 0, 0);
            sheet.addMergedRegion(region0);

            HSSFCell celldw = row1.createCell(1);
            celldw.setCellStyle(cellStyle);
            HSSFRichTextString sbdw2 = new HSSFRichTextString("申报单位");
            celldw.setCellValue(sbdw2);
            CellRangeAddress region1 = new CellRangeAddress(1, 2, 1, 1);
            sheet.addMergedRegion(region1);

            HSSFCell cellDlhsdw = row1.createCell(2);
            cellDlhsdw.setCellStyle(cellStyle);
            HSSFRichTextString dlhsdw = new HSSFRichTextString("独立核算单位");
            cellDlhsdw.setCellValue(dlhsdw);
            CellRangeAddress region2 = new CellRangeAddress(1, 2, 2, 2);
            sheet.addMergedRegion(region2);


            HSSFCell cellJdmc = row1.createCell(3);
            cellJdmc.setCellStyle(cellStyle);
            HSSFRichTextString jdmc = new HSSFRichTextString("节点名称");
            cellJdmc.setCellValue(jdmc);
            CellRangeAddress region3 = new CellRangeAddress(1, 2, 3, 3);
            sheet.addMergedRegion(region3);

            HSSFCell cellXmm = row1.createCell(4);
            cellXmm.setCellStyle(cellStyle);
            HSSFRichTextString xmm = new HSSFRichTextString("项目名");
            cellXmm.setCellValue(xmm);
            CellRangeAddress region4 = new CellRangeAddress(1, 2, 4, 4);
            sheet.addMergedRegion(region4);

            HSSFCell cellZe = row1.createCell(5);
            cellZe.setCellStyle(cellStyle);
            HSSFRichTextString ze = new HSSFRichTextString("总额");
            cellZe.setCellValue(ze);
            CellRangeAddress region5 = new CellRangeAddress(1, 2, 5, 5);
            sheet.addMergedRegion(region5);

            HSSFCell cellHpqd = row1.createCell(6);
            cellHpqd.setCellStyle(cellStyle);
            HSSFRichTextString hpqd = new HSSFRichTextString("项目核批渠道");
            cellHpqd.setCellValue(hpqd);
            CellRangeAddress region6 = new CellRangeAddress(1, 2, 6, 6);
            sheet.addMergedRegion(region6);

            HSSFCell cellHpqdje = row1.createCell(7);
            cellHpqdje.setCellStyle(cellStyle);
            HSSFRichTextString hpqdje = new HSSFRichTextString("核批渠道金额");
            cellHpqdje.setCellValue(hpqdje);
            CellRangeAddress region7 = new CellRangeAddress(1, 2, 7, 7);
            sheet.addMergedRegion(region7);

            HSSFCell cellZfcg = row1.createCell(8);
            cellZfcg.setCellStyle(cellStyle);
            HSSFRichTextString zfcg = new HSSFRichTextString("政府采购");
            cellZfcg.setCellValue(zfcg);
            CellRangeAddress region8 = new CellRangeAddress(1, 1, 8, 11);
            sheet.addMergedRegion(region8);


            HSSFCell cellHwgz = row2.createCell(8);
            cellHwgz.setCellStyle(cellStyle);
            HSSFRichTextString hwgz = new HSSFRichTextString("货物购置");
            cellHwgz.setCellValue(hwgz);

            HSSFCell cellGc = row2.createCell(9);
            cellGc.setCellStyle(cellStyle);
            HSSFRichTextString gc = new HSSFRichTextString("工程");
            cellGc.setCellValue(gc);

            HSSFCell cellFw = row2.createCell(10);
            cellFw.setCellStyle(cellStyle);
            HSSFRichTextString fw = new HSSFRichTextString("服务");
            cellFw.setCellValue(fw);

            HSSFCell cellXecg = row2.createCell(11);
            cellXecg.setCellStyle(cellStyle);
            HSSFRichTextString xecg = new HSSFRichTextString("小额采购");
            cellXecg.setCellValue(xecg);

            HSSFCell cellGmfw = row1.createCell(12);
            cellGmfw.setCellStyle(cellStyle);
            HSSFRichTextString gmfw = new HSSFRichTextString("政府购买服务");
            cellGmfw.setCellValue(gmfw);
            CellRangeAddress region13 = new CellRangeAddress(1, 2, 12, 12);
            sheet.addMergedRegion(region13);
            //-----------表头添加结束


            //dataCellStyle用于数据表格样式
            HSSFCellStyle dataCellStyle = workbook.createCellStyle();
            //设置水平居中
            dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            dataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            //总计
            BigDecimal sumze = new BigDecimal("0");
            BigDecimal sumhpqdje = new BigDecimal("0");
            BigDecimal sumgz = new BigDecimal("0");
            BigDecimal sumgc = new BigDecimal("0");
            BigDecimal sumfw = new BigDecimal("0");
            BigDecimal sumcg = new BigDecimal("0");
            BigDecimal sumzfgmfw = new BigDecimal("0");

            //-------开始添加数据
            //创建行下标
            int rowIndex = 3;
            int endRowIndex = 3;
            int number = 1;
            for (int i = 0; i < data.size(); i++) {

                List<BudgetSection> list = data.get(i).getList();
                for (int j = 0; j < list.size(); j++) {
                    HSSFRow newRow = null;

                    Map<String, Object> xmhpqd = list.get(j).getXmhpqd();

                    //先写入项目渠道 以及核批渠道金额
                    int ix = 0;
                    for (Map.Entry<String,Object> entry:xmhpqd.entrySet()) {
                        if (ix==0){
                            newRow = sheet.createRow(endRowIndex);
                            newRow.setHeightInPoints(20);

                            //项目核批渠道
                            HSSFCell xmhpqdCell = newRow.createCell(6);
                            xmhpqdCell.setCellValue(entry.getKey());
                            xmhpqdCell.setCellStyle(dataCellStyle);
                            //核批渠道金额
                            HSSFCell hpqdjeCell = newRow.createCell(7);
                            hpqdjeCell.setCellValue(Double.parseDouble(entry.getValue().toString()));
                            hpqdjeCell.setCellStyle(dataCellStyle);

                            endRowIndex++;
                            ix = 1;
                        }else{
                            HSSFRow newtempRow = sheet.createRow(endRowIndex);
                            newtempRow.setHeightInPoints(20);

                            //项目核批渠道
                            HSSFCell xmhpqdCell = newtempRow.createCell(6);
                            xmhpqdCell.setCellValue(entry.getKey());
                            xmhpqdCell.setCellStyle(dataCellStyle);
                            //核批渠道金额
                            HSSFCell hpqdjeCell = newtempRow.createCell(7);
                            hpqdjeCell.setCellValue(Double.parseDouble(entry.getValue().toString()));
                            hpqdjeCell.setCellStyle(dataCellStyle);

                            endRowIndex++;
                        }
                    }

                    newRow.createCell(0).setCellValue(number);

                    newRow.createCell(1).setCellValue(list.get(j).getSbdw());//申报单位

                    newRow.createCell(2).setCellValue(list.get(j).getSzdlhsdw());//独立和算单位
                    newRow.createCell(3).setCellValue(list.get(j).getJdmc());//节点名称

                    newRow.createCell(4).setCellValue(list.get(j).getXmmc());//项目名称

                    newRow.createCell(5).setCellValue(Double.parseDouble(list.get(j).getZe()));//总额

                    newRow.createCell(8).setCellValue(Double.parseDouble(list.get(j).getHwgz()));

                    newRow.createCell(9).setCellValue(Double.parseDouble(list.get(j).getGc()));

                    newRow.createCell(10).setCellValue(Double.parseDouble(list.get(j).getFw()));

                    newRow.createCell(11).setCellValue(Double.parseDouble(list.get(j).getXecg()));

                    newRow.createCell(12).setCellValue(Double.parseDouble(list.get(j).getZfgmfw()));

                    //如果核批数据>=2则合并其他表格行
                    if (rowIndex != endRowIndex - 1){

                        CellRangeAddress reg0 = new CellRangeAddress(rowIndex, endRowIndex -1, 0, 0);
                        sheet.addMergedRegion(reg0);

                        CellRangeAddress reg1 = new CellRangeAddress(rowIndex, endRowIndex -1, 1, 1);
                        sheet.addMergedRegion(reg1);

                        CellRangeAddress reg2 = new CellRangeAddress(rowIndex, endRowIndex -1, 2, 2);
                        sheet.addMergedRegion(reg2);

                        CellRangeAddress reg3 = new CellRangeAddress(rowIndex, endRowIndex -1, 3, 3);
                        sheet.addMergedRegion(reg3);

                        CellRangeAddress reg4 = new CellRangeAddress(rowIndex, endRowIndex -1, 4, 4);
                        sheet.addMergedRegion(reg4);
                        CellRangeAddress reg5 = new CellRangeAddress(rowIndex, endRowIndex -1, 5, 5);
                        sheet.addMergedRegion(reg5);
//                        CellRangeAddress reg6 = new CellRangeAddress(rowIndex, endRowIndex -1, 6, 6);
//                        sheet.addMergedRegion(reg6);
//                        CellRangeAddress reg7 = new CellRangeAddress(rowIndex, endRowIndex -1, 7, 7);
//                        sheet.addMergedRegion(reg7);

                        CellRangeAddress reg8= new CellRangeAddress(rowIndex, endRowIndex -1, 8, 8);
                        sheet.addMergedRegion(reg8);

                        CellRangeAddress reg9 = new CellRangeAddress(rowIndex, endRowIndex -1, 9, 9);
                        sheet.addMergedRegion(reg9);

                        CellRangeAddress reg10 = new CellRangeAddress(rowIndex, endRowIndex -1, 10, 10);
                        sheet.addMergedRegion(reg10);

                        CellRangeAddress reg11 = new CellRangeAddress(rowIndex, endRowIndex -1, 11, 11);
                        sheet.addMergedRegion(reg11);

                        CellRangeAddress reg12 = new CellRangeAddress(rowIndex, endRowIndex -1, 12, 12);
                        sheet.addMergedRegion(reg12);
                    }

                    //设置表格居中
//                    for (int k = 0; k < 12; k++) {
//                        if (k == 6 || k == 7)
//                            continue;
//                        newRow.getCell(k).setCellStyle(dataCellStyle);
//                    }
                    for (int k = 0; k < 12; k++) {
                        newRow.getCell(k).setCellStyle(dataCellStyle);
                    }
                    number++;
                    rowIndex = endRowIndex;
                }
                //添加小计
                HSSFRow sumRow = sheet.createRow(rowIndex);
                sumRow.setHeightInPoints(20);

                HSSFCell xjCell = sumRow.createCell(4);
                xjCell.setCellStyle(cellStyle);
                HSSFRichTextString xj = new HSSFRichTextString("小计");
                xjCell.setCellValue(xj);


                sumRow.createCell(5).setCellValue(Double.parseDouble(data.get(i).getZeTotal().toString()));
                sumRow.createCell(7).setCellValue(Double.parseDouble(data.get(i).getHpqdjeTotla().toString()));
                sumRow.createCell(8).setCellValue(Double.parseDouble(data.get(i).getGzTotla().toString()));
                sumRow.createCell(9).setCellValue(Double.parseDouble(data.get(i).getGcTotla().toString()));
                sumRow.createCell(10).setCellValue(Double.parseDouble(data.get(i).getFwTotla().toString()));
                sumRow.createCell(11).setCellValue(Double.parseDouble(data.get(i).getCgTotla().toString()));
                sumRow.createCell(12).setCellValue(Double.parseDouble(data.get(i).getZfgmTotla().toString()));
                //小计居中
//                for (int k = 4; k < 12; k++) {
//                    if (k == 4)
//                        continue;
//                    sumRow.getCell(k).setCellStyle(dataCellStyle);
//                }

                sumRow.setRowStyle(dataCellStyle);


                //获取总计数据
                sumze = sumze.add(new BigDecimal(data.get(i).getZeTotal().toString()));
                sumhpqdje = sumhpqdje.add(new BigDecimal(data.get(i).getHpqdjeTotla().toString()));
                sumgz = sumgz.add(new BigDecimal(data.get(i).getGzTotla().toString()));
                sumgc = sumgc.add(new BigDecimal(data.get(i).getGcTotla().toString()));
                sumfw = sumfw.add(new BigDecimal(data.get(i).getFwTotla().toString()));
                sumcg = sumcg.add(new BigDecimal(data.get(i).getGcTotla().toString()));
                sumzfgmfw = sumzfgmfw.add(new BigDecimal(data.get(i).getZfgmTotla().toString()));

                rowIndex++;
                endRowIndex++;
            }

            HSSFRow sumRow = sheet.createRow(rowIndex+1);
            sumRow.setHeightInPoints(20);

            HSSFCell zjCell = sumRow.createCell(4);
            zjCell.setCellStyle(cellStyle);
            HSSFRichTextString zj = new HSSFRichTextString("总计");
            zjCell.setCellValue(zj);

            //添加总计数据
            sumRow.createCell(5).setCellValue(Double.parseDouble(sumze.toString()));
            sumRow.createCell(7).setCellValue(Double.parseDouble(sumhpqdje.toString()));
            sumRow.createCell(8).setCellValue(Double.parseDouble(sumgz.toString()));
            sumRow.createCell(9).setCellValue(Double.parseDouble(sumgc.toString()));
            sumRow.createCell(10).setCellValue(Double.parseDouble(sumfw.toString()));
            sumRow.createCell(11).setCellValue(Double.parseDouble(sumcg.toString()));
            sumRow.createCell(12).setCellValue(Double.parseDouble(sumzfgmfw.toString()));

            //总计居中
//            for (int k = 3; k < 12; k++) {
//                if (k == 4)
//                    continue;
//                sumRow.getCell(k).setCellStyle(dataCellStyle);
//            }
            sumRow.setRowStyle(dataCellStyle);

            //设置要导出的文件的名字
            String downFilename  = "info"  + ".xls";
            //服务器发给浏览器的数据是使用UTF-8编码
            response.setContentType("application/octet-stream ; charset=ISO-8859-1 ");

            //设置浏览器的编码为UTF-8
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(downFilename,"UTF-8"));

            response.flushBuffer();
            workbook.write(response.getOutputStream());



            msgMap.put("code","200");
            msgMap.put("msg","");
            msgMap.put("data","");
        } catch (IOException e) {
            e.printStackTrace();
            msgMap.put("code","500");
            msgMap.put("msg","程序异常，请联系管理员！");
            msgMap.put("data","");
        }
        return JSON.toJSONString(msgMap);
    }
}
